import datetime
import requests
import pandas as pd
import pymysql
from sklearn.preprocessing import MinMaxScaler
from joblib import dump

class WeatherUtils(object):
    """天气工具类
    
    Args:
        object (_type_): 基础对象类型
    """
    
    def __init__(self):
        self.url = 'http://v1.yiketianqi.com/api'
        self.date = [
            '2024-07-01',
            '2024-08-01',
            '2024-09-01',
            '2024-10-01',
            '2024-11-01',
            '2024-12-01',
        ]
    
    def get_data(self):
        """获取天气数据
        
        Returns:
            list: 包含天气数据的列表
        """
        data_list = []
        for d in self.date:
            conf = {
                'appid': "64893898",  # 用自己注册后的appid
                'appsecret': "m0fCd5a7",
                'version': "history",
                'year': d[:4],  # 修正为获取年份
                'month': d[5:7],
                'city': "南昌"
            }
            res = requests.get(self.url, params=conf)
            res_data = res.json()
            
            for i in res_data['data']:
                data_list.append({
                    'date': datetime.datetime.strptime(i['yms'], '%Y-%m-%d'),
                    'bwendu': i['bwendu'],
                    'ywendu': i['ywendu'],
                    'tianqi': i['tianqi'],
                    'fengli': i['fengli']
                })
            
        df = pd.DataFrame(data_list)
        df.to_csv('./timing/weather.csv', index=False)
        return df

class MysqlUtils(object):
    def __init__(self):
        self.conn = pymysql.connect(
            host='127.0.0.1',
            user='root',
            passwd='root',
            db='scenic',
            port=3306,
            charset='utf8'
        )
        self.weather_data = pd.read_csv('./timing/weather.csv')
    
    def is_holiday(self, date):
        """判断是否为节假日
        """
        if date in ['2024-09-03', '2024-10-01', '2024-10-02', '2024-10-03','2024-10-04', '2024-10-05', '2024-10-06', '2024-10-07','2025-01-01', '2025-01-02', '2025-01-03']:
            return 1
        return 0

    def get_scenic_data(self):
        """获取景区数据
        """
        cursor = self.conn.cursor(cursor=pymysql.cursors.DictCursor)
        sql = """
        SELECT DATE(g.create_time) as date, COUNT(*) as count
        FROM order_user_date_pel g
        WHERE DATE(g.create_time) < '2025-01-01' GROUP BY date
        """
        cursor.execute(sql)
        ret = cursor.fetchall()
        df = pd.DataFrame(ret)
        print(df.head())
        # 合并天气数据
        self.weather_data['date'] = pd.to_datetime(self.weather_data['date'])
        df['date'] = pd.to_datetime(df['date'])
        # 格式转换
        df_pivot = pd.merge(self.weather_data, df, on='date')

        df_pivot.set_index('date', inplace=True)

        # 转化温度
        df_pivot['bwendu'] = df_pivot['bwendu'].str.replace("°", "").astype(int)  # 修正列名和变量名
        df_pivot['ywendu'] = df_pivot['ywendu'].str.replace("°", "").astype(int)  # 修正列名和变量名

        # print(df_pivot.head())
        df_pivot['dow'] = df_pivot.index.dayofweek  # 星期几 (0-6)
        df_pivot['month'] = df_pivot.index.month  # 月份
        df_pivot['is_holiday'] = df_pivot.index.to_series().apply(lambda x: self.is_holiday(x.strftime('%Y-%m-%d')))

        # 对星期几和月份进行单独编码
        df_pivot = pd.get_dummies(df_pivot, columns=['dow', 'month', 'tianqi', 'fengli'], dtype=int)

        # 归一化入园数
        scaler = MinMaxScaler()
        print(df_pivot.head())
        feature = df_pivot[['count']]  # 修正特征选择
        df_pivot['count_norm'] = scaler.fit_transform(feature)  # 修正列名和赋值

        # 归一化天气
        weather_feature = df_pivot[['bwendu', 'ywendu']]  # 修正列名
        dump(scaler, './timing/scaler.joblib')  # 修正路径
        dump(weather_feature, './timing/weather_feature.joblib')  # 修正路径
        print(df_pivot.head())
        df_pivot.to_csv('./timing/scenic_data.csv', index=False)
        
if __name__ == '__main__': 
    mu = MysqlUtils()  
    mu.get_scenic_data()